IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetSupervisorsByOrganization]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetSupervisorsByOrganization]
GO
 
SET QUOTED_IDENTIFIER OFF
GO
/******************************************************************              
* Name:     [GetSupervisorsByOrganization]              
* Purpose:  Get the list of supervisors available for organization/unitDept(s)
* PARAMETERS(IN)              
* Name          Description                   
* -------------      -------------------------------------------              
*	@organizationId  -- Organization Id   
*	@unitDept 	 -- Unit/Dept Id
*	@firstName 	 -- FirstName
*	@lastName 	 -- LastName
*	@userName 	 -- UserName
                 
*********************************************************************/ 
CREATE PROCEDURE dbo.[GetSupervisorsByOrganization]
(  
 @organizationId int,  
 @unitDept varchar(max),  
 @firstName varchar(64),  
 @lastName varchar(64),  
 @userName varchar(36)   
)  
AS BEGIN  
 if @firstName is not null  
  Set @firstName = @firstName +'%'  
 else Set @firstName = '%'  
  
 if @lastName is not null  
  Set @lastName = @lastName +'%'  
 else Set @lastName = '%'  
  
 if @userName is not null  
  Set @userName = @userName +'%'  
 else Set @userName = '%'  
  
 IF (@organizationId > 0)    
  BEGIN  
    IF @unitDept is not null  
     BEGIN  
       Select adp.[Id] as [PersonId],rtrim(ltrim(adp.[LastName])) + ', '+ rtrim(ltrim(adp.[FirstName])) as [Name]      
       from AdmPerson adp      
        inner JOIN FWKApplicationUser fac      
        on adp.[fwkDomainUserId]=fac.[FwkDomainUserId]           
        inner JOIN FwkDomainUser fdu      
        on adp.[fwkDomainUserId]=fdu.[Id]  
       inner join AdmInternalPerson aip  
        on adp.Id = aip.AdmPersonId  
       inner join FwkDomainOrganization fdo  
        on fdo.Id = aip.PrimaryOrganizationId  
       WHERE   
          (fdo.InstitutionId = @organizationId and fdo.Id in (Select cast(VALUE as int)as Value  from GetValueTableOfDelimittedString(@unitDept,',')))  
          AND     
         ((fac.[ModuleRoleBitmask] & 512)>0)     
          AND  
          ISNULL(adp.[firstName], '*') like @firstName              
          AND              
          ISNULL(adp.[lastName], '*') like @lastName              
          AND              
          ISNULL(fdu.[userName], '*') like @userName                
          Order by adp.[LastName]  
     END  
    
     ELSE IF @unitDept is null  
      BEGIN  
       Select adp.[Id] as [PersonId],rtrim(ltrim(adp.[LastName])) + ', '+ rtrim(ltrim(adp.[FirstName])) as [Name]       
         from AdmPerson adp      
          inner JOIN FWKApplicationUser fac      
          on adp.[fwkDomainUserId]=fac.[FwkDomainUserId]           
          inner JOIN FwkDomainUser fdu      
          on adp.[fwkDomainUserId]=fdu.[Id]  
         inner join AdmInternalPerson aip  
          on adp.Id = aip.AdmPersonId  
         inner join FwkDomainOrganization fdo  
          on fdo.Id = aip.PrimaryOrganizationId  
         WHERE   
            (fdo.InstitutionId = @organizationId)  
            AND     
           ((fac.[ModuleRoleBitmask] & 512)>0)     
            AND  
            ISNULL(adp.[firstName], '*') like @firstName              
            AND              
            ISNULL(adp.[lastName], '*') like @lastName              
            AND              
            ISNULL(fdu.[userName], '*') like @userName  
            Order by adp.[LastName]  
      END  
  END  
    ELSE IF(@organizationId = 0)  
  BEGIN    
   Select adp.[Id] as [PersonId],rtrim(ltrim(adp.[LastName])) + ', '+ rtrim(ltrim(adp.[FirstName])) as [Name]       
     from AdmPerson adp      
     inner JOIN FWKApplicationUser fac      
     on adp.[fwkDomainUserId]=fac.[FwkDomainUserId]           
     inner JOIN FwkDomainUser fdu      
     on adp.[fwkDomainUserId]=fdu.[Id]  
    inner join AdmInternalPerson aip  
     on adp.Id = aip.AdmPersonId  
    inner join FwkDomainOrganization fdo  
     on fdo.Id = aip.PrimaryOrganizationId  
    WHERE   
       ((fac.[ModuleRoleBitmask] & 512)>0)     
       AND  
       ISNULL(adp.[firstName], '*') like @firstName              
       AND              
       ISNULL(adp.[lastName], '*') like @lastName              
       AND              
       ISNULL(fdu.[userName], '*') like @userName                
       Order by adp.[LastName]  
  END       
END  
  
  
  
  